/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package beans;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import model.Guest;
import model.Meeting;
import model.Room;
import model.impl.GuestMysqlImpl;
import model.impl.MeetingMysqlImpl;
import model.impl.RoomMysqlImpl;
public class Dao {
    public Connection conexion;
    public final static String userDb = "root";
    public final static String passDb = "";
   // private int id;
    
    
    //Conectar a la Base de datos
    public void conectar() throws SQLException,ClassNotFoundException{
        Class.forName("com.mysql.jdbc.Driver");
         conexion=DriverManager.getConnection("jdbc:mysql://localhost:3306/meetingplanner",userDb, passDb);
        
    }
    //Desconectar a la Base de datos
    public void desconectar() throws SQLException, ClassNotFoundException{
        conexion.close();
    }
    
    //Metodo para consultar si un email y contraseñan pertenecen a una cuenta 
    //de manager registrada
    public boolean isAcountMExists(String email, String password) throws SQLException{
        String sql = "SELECT * FROM manager WHERE email='"+email+"' AND password='"+password+"'";
        PreparedStatement ps = conexion.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        
        return rs.next();
    }
   
     //Metodo para consultar si un email y contraseñan pertenecen a una cuenta 
    // de organizer registrada
    public boolean isAcountOExists(String email, String password) throws SQLException{
        String sql = "SELECT * FROM organizer WHERE email='"+email+"' AND password='"+password+"'";
        PreparedStatement ps = conexion.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        
        return rs.next();
    }
    
    //Metodo para consultar si el email de Manager recibido ya esta registrado
    public boolean isEmailMRegistered(String email) throws SQLException{
        String sql = "SELECT * FROM manager WHERE email='"+email+"'";
        PreparedStatement ps = conexion.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
 
        return rs.next();
    }
    
    //Metodo para consultar si el de Organizer email recibido ya esta registrado
    public boolean isEmailORegistered(String email) throws SQLException{
        String sql = "SELECT * FROM organizer WHERE email='"+email+"'";
        PreparedStatement ps = conexion.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
 
        return rs.next();
    }
    
    //Metodo para consultar si una room ya esta registrada
    public boolean isRoomRegistered(String name) throws SQLException{
        String sql = "SELECT * FROM room WHERE name='"+name+"'";
        PreparedStatement ps = conexion.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
 
        return rs.next();
    }
    
    //Metodo para registrar una cuenta de manager
    public void registerManager(String email, String password, String name) throws SQLException{
        //id++;
        String sql = "INSERT INTO `manager`(`email`,`password`,`name`) VALUES ('"+email+"','"+password+"','"+name+"')";
        PreparedStatement ps = conexion.prepareStatement(sql);
        ps.executeUpdate();
    }
    
     //Metodo para registrar una cuenta de organizer
    public void registerOrganizer(String email, String password, String name) throws SQLException{
       
        String sql = "INSERT INTO `organizer`(`email`,`password`,`name`) VALUES ('"+email+"','"+password+"','"+name+"')";
        PreparedStatement ps = conexion.prepareStatement(sql);
        ps.executeUpdate();
    }
    
     //Metodo para registrar una room
    public void registerRoom(String name, String place, Integer capacity, String caracteristics) throws SQLException{
        String sql = "INSERT INTO `room`(`name`,`place`,`capacity`,`caracteristics`) VALUES ('"+name+"','"+place+"','"+capacity+"','"+caracteristics+"')";
        PreparedStatement ps = conexion.prepareStatement(sql);
        ps.executeUpdate();
    }
    
     //Metodo para registrar una room
    public void registerInvitation(Integer id_org, Integer id_room, String name, String description, String data, String guests)throws SQLException{
        String sql = "INSERT INTO `invitation`(`ID_org`,`ID_room`,`name`,`description`,`date`,`guest`) VALUES ('"+id_org+"','"+id_room+"','"+name+"','"+description+"','"+data+"','"+guests+"')";
        PreparedStatement ps = conexion.prepareStatement(sql);
        ps.executeUpdate();
    }
    
    public void registerMeeting(Integer id_inv, String hourI, String hourF)throws SQLException{
        Integer room=getIdRoomByInvitation(id_inv);
        String data=getDateByInvitation(id_inv);
        String sql = "INSERT INTO `meeting`(`id_room`,`day`,`hour_init`,`hour_final`) VALUES ('"+room+"','"+data+"','"+hourI+"','"+hourF+"')";
        PreparedStatement ps = conexion.prepareStatement(sql);
        ps.executeUpdate();
    }
    
    public Integer getIdByNameRoom(String name) throws SQLException{
        String sql = "SELECT * FROM room WHERE name='"+name+"'";
        PreparedStatement ps = conexion.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        
        if(rs.next()){
            return Integer.parseInt(rs.getString("id"));
        }
        
        return 0;
    }
    
    public Integer getIdOrgByName(String name)throws SQLException{
        String sql = "SELECT * FROM organizer WHERE name='"+name+"'";
        PreparedStatement ps = conexion.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        
        if(rs.next()){
            return Integer.parseInt(rs.getString("id"));
        }
        
        return 0;
    }
    
    public Integer getIdInv(Integer id_org, Integer id_room, String name)throws SQLException{
        String sql = "SELECT * FROM invitation WHERE ID_org='"+id_org+"' AND ID_room='"+id_room+"' AND name='"+name+"'";
        PreparedStatement ps = conexion.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        
        if(rs.next()){
            return Integer.parseInt(rs.getString("ID"));
        }
        
        return 0;
    }
    
    public Integer getCapacityByIdRoom(Integer id) throws SQLException{
        String sql = "SELECT * FROM room WHERE ID='"+id+"'";
        PreparedStatement ps = conexion.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        
        if(rs.next()){
            return Integer.parseInt(rs.getString("capacity"));
        }
        
        return 0;
    }
    
     public String getNameMByEmail(String email) throws SQLException{
        String sql = "SELECT * FROM manager WHERE email='"+email+"'";
        PreparedStatement ps = conexion.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        
        if(rs.next()){
            return rs.getString("name");
        }
        
        return null;
    }
     
     public String getNameOByEmail(String email) throws SQLException{
        String sql = "SELECT * FROM organizer WHERE email='"+email+"'";
        PreparedStatement ps = conexion.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        
        if(rs.next()){
            return rs.getString("name");
        }
        
        return null;
    }
     
      public static Integer getIdRoomByInvitation(Integer id) throws SQLException{
           try{
          Class.forName("org.gjt.mm.mysql.Driver");
         Connection conexion = DriverManager.getConnection(
            "jdbc:mysql://localhost:3306/meetingplanner",userDb, passDb);
       Statement st = conexion.createStatement();
         ResultSet rs = st.executeQuery("SELECT * FROM invitation WHERE ID='"+id+"'");
        
        if(rs.next()){
            return Integer.parseInt(rs.getString("id_room"));
        }
         rs.close();
         st.close();
         conexion.close();
       }catch (Exception e){
         e.printStackTrace();
      }
        return 0;
    }
    
      public static String getDateByInvitation(Integer id) throws SQLException{
           try{
          Class.forName("org.gjt.mm.mysql.Driver");
         Connection conexion = DriverManager.getConnection(
            "jdbc:mysql://localhost:3306/meetingplanner",userDb, passDb);
      Statement st = conexion.createStatement();
         ResultSet rs = st.executeQuery("SELECT * FROM invitation WHERE ID='"+id+"'");
        if(rs.next()){
            return rs.getString("date");
        }
         rs.close();
         st.close();
         conexion.close();
       }catch (Exception e){
         e.printStackTrace();
      }
        return null;
    }
      
      public static List<Room> getRooms() throws SQLException{
        List<Room> result = new ArrayList<Room>();
       try{
          Class.forName("org.gjt.mm.mysql.Driver");
         Connection conexion = DriverManager.getConnection(
            "jdbc:mysql://localhost:3306/meetingplanner",userDb, passDb);
         Statement st = conexion.createStatement();
         ResultSet rs = st.executeQuery("SELECT * FROM room");
         while (rs.next()){
            Room room = new RoomMysqlImpl();
            room.setKey(rs.getInt("ID"));
            room.setName(rs.getString("name"));
            room.setPlace(rs.getString("place"));
            room.setCapacity(rs.getInt("capacity"));
            room.setProperties(rs.getString("caracteristics"));
            result.add(room);
         }
         rs.close();
         st.close();
         conexion.close();
       }catch (Exception e){
         e.printStackTrace();
      }
        return result;
     }
      
      public static List<Meeting> getMeetingsByRoomAndDay(Integer id_inv)throws SQLException{
        List<Meeting> result = new ArrayList<Meeting>();
        List<Meeting> aux = new ArrayList<Meeting>();
       try{
          Class.forName("org.gjt.mm.mysql.Driver");
         Connection conexion = DriverManager.getConnection(
            "jdbc:mysql://localhost:3306/meetingplanner",userDb, passDb);
         Integer id_room=getIdRoomByInvitation(id_inv);
         String data= getDateByInvitation(id_inv);
         Statement st = conexion.createStatement();
         ResultSet rs = st.executeQuery("SELECT * FROM meeting WHERE ID_room='"+id_room+"'");
         while (rs.next()){
            Meeting m = new MeetingMysqlImpl();
            m.setKey(rs.getInt("ID"));
            m.setIdRoom(rs.getInt("id_inv"));
            m.setDay(rs.getString("day"));
            m.setHourInit(rs.getString("hour_init"));
            m.setHourFinal(rs.getString("hour_final"));
            aux.add(m);
         }
         for(Meeting maux : aux){
             if(maux.getDay() == data){
                 result.add(maux);
             }
         }
         rs.close();
         st.close();
         conexion.close();
       }catch (Exception e){
         e.printStackTrace();
      }
        return result;
     }
      
      public static void saveGuestsByInvitation(Integer id_inv)throws SQLException{
       try{
          Class.forName("org.gjt.mm.mysql.Driver");
         Connection conexion = DriverManager.getConnection(
            "jdbc:mysql://localhost:3306/meetingplanner",userDb, passDb);
         Integer id_room=getIdRoomByInvitation(id_inv);
         String data= getDateByInvitation(id_inv);
         Statement st = conexion.createStatement();
         ResultSet rs = st.executeQuery("SELECT * FROM invitation WHERE id='"+id_inv+"'");  
         while (rs.next()){
            String g1=rs.getString("guest");
            String[] emails=g1.split(" ");
            for(String s: emails){
                String sql = "INSERT INTO `guest`(`email`,`id_inv`) VALUES ('"+s+"','"+id_inv+"')";
                PreparedStatement ps = conexion.prepareStatement(sql);
                ps.executeUpdate();
            }
         }
         rs.close();
         st.close();
         conexion.close();
       }catch (Exception e){
         e.printStackTrace();
      }
     }
      
      public void modifRoomByID(int id, String name, String place, int capacity, String caracteristics) throws SQLException{
          //Si el campo del name no esta vacio lo cambio
          if(!name.isEmpty()){
              String sql = "UPDATE room SET name='"+name+"' WHERE id='"+id+"'";
              PreparedStatement ps = conexion.prepareStatement(sql);
              ps.executeUpdate();
          }
           //Si el campo del place no esta vacio lo cambio
          if(!place.isEmpty()){
              String sql = "UPDATE room SET place='"+place+"' WHERE id='"+id+"'";
              PreparedStatement ps = conexion.prepareStatement(sql);
              ps.executeUpdate();
          }
           //Si el campo del capacity no esta vacio lo cambio
          if(capacity!=0){
              String sql = "UPDATE room SET capacity='"+capacity+"' WHERE id='"+id+"'";
              PreparedStatement ps = conexion.prepareStatement(sql);
              ps.executeUpdate();
          }
            //Si el campo del capacity no esta vacio lo cambio
          if(!caracteristics.isEmpty()){
              String sql = "UPDATE room SET caracteristics='"+caracteristics+"' WHERE id='"+id+"'";
              PreparedStatement ps = conexion.prepareStatement(sql);
              ps.executeUpdate();
          }
      }
      
       public void saveHoursInv(Integer id_inv, String hourI, String hourF) throws SQLException{
              String sql = "UPDATE invitation SET init='"+hourI+"' WHERE ID='"+id_inv+"'";
              PreparedStatement ps = conexion.prepareStatement(sql);
              ps.executeUpdate();
              
              String sql1 = "UPDATE invitation SET fin='"+hourF+"' WHERE ID='"+id_inv+"'";
              PreparedStatement ps1 = conexion.prepareStatement(sql1);
              ps1.executeUpdate();
          
       }
      
}